package com.toycloud.business.service.impl;

import com.toycloud.business.dto.params.FeedbackConfigTypeParams;
import com.toycloud.business.dto.params.ProductQuantityParams;
import com.toycloud.business.dto.results.CommonResult;
import com.toycloud.business.dto.results.ProductProblemQuantity;
import com.toycloud.business.dto.results.ProductQualityAnalysis;
import com.toycloud.business.dto.results.ProductQualityTrend;
import com.toycloud.business.service.IProductQualityAnalysisService;
import com.toycloud.common.exception.ServiceException;
import com.toycloud.system.domain.dto.FeedbackAnalysisDataFactorDto;
import com.toycloud.system.domain.feedback.FeedbackAnalysisData;
import com.toycloud.system.domain.feedback.FeedbackConfig;
import com.toycloud.system.domain.statistic.TempProductData;
import com.toycloud.system.mapper.feedback.FeedbackAnalysisDataMapper;
import com.toycloud.system.mapper.feedback.FeedbackConfigMapper;
import com.toycloud.system.mapper.statistic.TempProductDataMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.time.temporal.WeekFields;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @author hlchan
 * @version 1.0
 * @date 2024/10/30 15:25
 */
@Service
public class ProductQualityAnalysisServiceImpl implements IProductQualityAnalysisService {

    @Autowired
    FeedbackAnalysisDataMapper feedbackAnalysisDataMapper;
    @Autowired
    FeedbackConfigMapper feedbackConfigMapper;
    @Autowired
    TempProductDataMapper tempProductDataMapper;

    public static HashMap<Integer, String> PERIOD_MAP = new HashMap<Integer, String>() {{
        put(1, "day");
        put(2, "weekly");
        put(3, "monthly");
    }};

    public static List<String> CLOSE_LIST = new ArrayList<String>() {{
        add("PTKEXnnE");
    }};

    public ProductQualityAnalysis getProductQualityAnalysis(List<FeedbackAnalysisDataFactorDto> feedbackAnalysisDataList, ProductQuantityParams params) {
        // 获取映射关系
        List<FeedbackConfig> problemList = feedbackConfigMapper.selectByTypes(new String[]{"9"});
        Map<Integer, String> problemMap = problemList.stream()
                .collect(Collectors.toMap(FeedbackConfig::getId, FeedbackConfig::getName));

        ProductQualityAnalysis response = new ProductQualityAnalysis();

        // 创建日期列表（按周期：日、周或月）
        List<String> dateList = new ArrayList<>();
        LocalDate startDate = params.getStartTime() != null ? params.getStartTime() : feedbackAnalysisDataList.stream()
                .map(FeedbackAnalysisData::getFeedbackTime)
                .min(LocalDate::compareTo)
                .orElse(LocalDate.now());

        LocalDate endDate = params.getEndTime() != null ? params.getEndTime() : feedbackAnalysisDataList.stream()
                .map(FeedbackAnalysisData::getFeedbackTime)
                .max(LocalDate::compareTo)
                .orElse(LocalDate.now());

        LocalDate currentDate = startDate;

        // 设置日期范围，支持按日、周、月统计
        switch (PERIOD_MAP.get(params.getPeriod())) {
            case "weekly":
                // 遍历从 startDate 到 endDate 的每一周，格式为 "YYYY-WW"
                while (!currentDate.isAfter(endDate)) {
                    int weekOfYear = currentDate.get(WeekFields.ISO.weekOfWeekBasedYear());
                    int year = currentDate.get(WeekFields.ISO.weekBasedYear());
                    dateList.add( year + "-第" + String.format("%02d", weekOfYear)+"周");
                    currentDate = currentDate.plusWeeks(1);
                }
                break;
            case "monthly":
                // 遍历从 startDate 到 endDate 的每一月
                while (!currentDate.isAfter(endDate)) {
                    dateList.add(currentDate.format(DateTimeFormatter.ofPattern("yyyy-MM")));
                    currentDate = currentDate.plusMonths(1);
                }
                break;
            default:
                // 默认按日统计
                while (!currentDate.isAfter(endDate)) {
                    dateList.add(currentDate.format(DateTimeFormatter.ISO_LOCAL_DATE));
                    currentDate = currentDate.plusDays(1);
                }
                break;
        }
        response.setDateList(dateList);

        Map<String, ProductQualityAnalysis.TagData> tagMap = new TreeMap<>();

        // 初始化 tagMap 并为每个日期置 0
        for (FeedbackAnalysisData feedback : feedbackAnalysisDataList) {
            String tag = problemMap.get(Integer.parseInt(feedback.getProblem()));
            tagMap.putIfAbsent(tag, new ProductQualityAnalysis.TagData(tag));

            ProductQualityAnalysis.TagData tagData = tagMap.get(tag);
            dateList.forEach(date -> tagData.getPercentages().putIfAbsent(date, 0));
        }

        // 根据周期统计数据
        for (FeedbackAnalysisDataFactorDto feedback : feedbackAnalysisDataList) {
            String tag = problemMap.get(Integer.parseInt(feedback.getProblem()));
            tagMap.putIfAbsent(tag, new ProductQualityAnalysis.TagData(tag));
            String date;

            switch (PERIOD_MAP.get(params.getPeriod())) {
                case "weekly":
                    int weekOfYear = feedback.getFeedbackTime().get(WeekFields.ISO.weekOfWeekBasedYear());
                    int year = feedback.getFeedbackTime().get(WeekFields.ISO.weekBasedYear());
                    date =  year + "-第" + String.format("%02d", weekOfYear)+"周";  // "YYYY-WW" 格式
                    break;
                case "monthly":
                    date = feedback.getFeedbackTime()
                            .withDayOfMonth(1)
                            .format(DateTimeFormatter.ofPattern("yyyy-MM"));
                    break;
                default:
                    date = feedback.getFeedbackTime().format(DateTimeFormatter.ISO_LOCAL_DATE);
                    break;
            }

            tagMap.get(tag).getPercentages().merge(date, feedback.getFactor()>0? feedback.getFactor():1, Integer::sum);
        }

        response.setTagsData(new ArrayList<>(tagMap.values()));
        Collections.sort(response.getDateList());
        return response;
    }

    public CommonResult<ProductQualityAnalysis> getProblemList(ProductQuantityParams params) {
        List<FeedbackAnalysisDataFactorDto> feedbackAnalysisDataList = feedbackAnalysisDataMapper.selectFeedbackAnalysisData(
                params.getProducts(), params.getCategories(), params.getProblems(), params.getStartTime(), params.getEndTime(), null);

        if (CollectionUtils.isEmpty(feedbackAnalysisDataList)) {
            return CommonResult.Fail("没有查询到数据！");
        }

        return CommonResult.Success(getProductQualityAnalysis(feedbackAnalysisDataList, params));
    }

    @Override
    public CommonResult<ProductQualityAnalysis> getProblemResolveList(ProductQuantityParams params) {
        List<FeedbackAnalysisDataFactorDto> feedbackAnalysisDataList = feedbackAnalysisDataMapper.selectFeedbackAnalysisData(
                params.getProducts(), params.getCategories(), params.getProblems(), params.getStartTime(), params.getEndTime(), CLOSE_LIST);

        if (CollectionUtils.isEmpty(feedbackAnalysisDataList)) {
            return CommonResult.Fail("没有查询到数据！");
        }
        return CommonResult.Success(getProductQualityAnalysis(feedbackAnalysisDataList, params));
    }

    @Override
    public CommonResult<ProductProblemQuantity> getProblemQuantity() {
        List<FeedbackAnalysisDataFactorDto> feedbackAnalysisDataList = feedbackAnalysisDataMapper.selectFeedbackAnalysisData(null, null, null, null, null, null);

        // 获取映射关系
        List<FeedbackConfig> problemList = feedbackConfigMapper.selectByTypes(new String[]{"9"});
        Map<Integer, String> problemMap = problemList.stream()
                .collect(Collectors.toMap(FeedbackConfig::getId, FeedbackConfig::getName));

        // 根据问题类型名称分组，统计数量
        Map<String, Long> problemQuantityMap = feedbackAnalysisDataList.stream()
                .collect(Collectors.groupingBy(
                        data -> problemMap.getOrDefault(Integer.parseInt(data.getProblem()), "未知问题"),
                        Collectors.summingLong(data -> data.getFactor()>0?data.getFactor():1)
                ));

        // 创建 ProductProblemQuantity 实例
        ProductProblemQuantity productProblemQuantity = new ProductProblemQuantity();

        // 提取问题名称和数量
        String[] problemNames = problemQuantityMap.keySet().toArray(new String[0]);
        String[] quantities = problemQuantityMap.values().stream()
                .map(String::valueOf)  // 将数量转换为 String
                .toArray(String[]::new);

        // 设置问题名称和数量
        productProblemQuantity.setProblems(problemNames);
        productProblemQuantity.setQuantity(quantities);

        return CommonResult.Success(productProblemQuantity);
    }

    @Override
    public CommonResult<ProductQualityTrend> getProblemTrend(ProductQuantityParams params) {
        // 查找统计资源
        List<FeedbackConfig> problemList = feedbackConfigMapper.selectByTypes(new String[]{"9"});
        Map<Integer, String> problemMap = problemList.stream()
                .collect(Collectors.toMap(FeedbackConfig::getId, FeedbackConfig::getName));
        List<String> productCodeList = params.getProducts();
        if(!CollectionUtils.isEmpty(productCodeList)){
            // 获取映射关系
            List<FeedbackConfig> productList = feedbackConfigMapper.selectByTypes(new String[]{"2"});
            Map<String, List<String>> productMap = productList.stream()
                    .filter(config ->config.getRemark()!=null &&!config.getRemark().equals(""))
                    .collect(Collectors.toMap(
                            config -> String.valueOf(config.getId()),
                            config -> Arrays.asList(config.getRemark().split(","))
                    ));
            productCodeList = productCodeList.stream()
                    .map(productMap::get)
                    .filter(Objects::nonNull) // 过滤掉没有匹配到的项
                    .flatMap(List::stream)
                    .collect(Collectors.toList());
        }
        List<TempProductData> totalProductCount = tempProductDataMapper.selectExistCount(productCodeList, params.getStartTime(), params.getEndTime());
        if (totalProductCount.isEmpty()) return CommonResult.Fail("无数据！");
        ProductQualityTrend response = new ProductQualityTrend();
        Map<String, Integer> totalProductCountMap = new TreeMap<>();
        Map<String, Integer> problemCountMap = new TreeMap<>();
        Map<String, Integer> productCountMap = new TreeMap<>();
        Map<String,Map<String,Integer>> problemDivideMapMap = new TreeMap<>();
        // 设置日期范围，支持按日、周、月统计
        switch (PERIOD_MAP.get(params.getPeriod())) {
            case "weekly":
                // 按周分组，每周取最后一天数据
                WeekFields weekFields = WeekFields.ISO;
                totalProductCountMap = totalProductCount.stream()
                        .collect(Collectors.groupingBy(
                                entry -> {
                                    int weekOfYear = entry.getCreatedate().get(WeekFields.ISO.weekOfWeekBasedYear());
                                    int year = entry.getCreatedate().get(WeekFields.ISO.weekBasedYear());
                                    return year + "-第" + String.format("%02d", weekOfYear)+"周";  // "YYYY-WW" 格式
                                },
                                Collectors.collectingAndThen(
                                        Collectors.groupingBy(
                                                TempProductData::getCreatedate,
                                                Collectors.summingInt(TempProductData::getTotalactivate)
                                        ),
                                        dateMap -> dateMap.entrySet().stream()
                                                .max(Map.Entry.comparingByKey())
                                                .map(Map.Entry::getValue)
                                                .orElse(0)
                                )
                        ));
                break;
            case "monthly":
                // 按月分组，取每月的最后一天数据
                totalProductCountMap = totalProductCount.stream()
                        .collect(Collectors.groupingBy(
                                entry -> entry.getCreatedate().with(TemporalAdjusters.firstDayOfMonth()).toString().substring(0, 7),
                                Collectors.collectingAndThen(
                                        Collectors.groupingBy(
                                                TempProductData::getCreatedate,
                                                Collectors.summingInt(TempProductData::getTotalactivate)
                                        ),
                                        dateMap -> dateMap.entrySet().stream()
                                                .max(Map.Entry.comparingByKey())
                                                .map(Map.Entry::getValue)
                                                .orElse(0)
                                )
                        ));
                break;
            case "day":
            default:
                // 按天分组
                totalProductCountMap = totalProductCount.stream()
                        .collect(Collectors.toMap(
                                entry -> entry.getCreatedate().toString(),
                                TempProductData::getTotalactivate,
                                Integer::sum // 当 key 重复时，执行加法合并
                        ));
                break;
        }

        // 创建日期列表（按周期：日、周或月）
        List<String> dateList = new ArrayList<>();
        LocalDate startDate = params.getStartTime() != null ? params.getStartTime() : totalProductCount.stream()
                .map(TempProductData::getCreatedate)
                .min(LocalDate::compareTo)
                .orElse(LocalDate.now());

        LocalDate endDate = params.getEndTime() != null ? params.getEndTime() : totalProductCount.stream()
                .map(TempProductData::getCreatedate)
                .max(LocalDate::compareTo)
                .orElse(LocalDate.now());

        LocalDate currentDate = startDate;
        // 设置日期范围，支持按日、周、月统计
        switch (PERIOD_MAP.get(params.getPeriod())) {
            case "weekly":
                // 遍历从 startDate 到 endDate 的每一周，格式为 "YYYY-WW"
                while (!currentDate.isAfter(endDate)) {
                    int weekOfYear = currentDate.get(WeekFields.ISO.weekOfWeekBasedYear());
                    int year = currentDate.get(WeekFields.ISO.weekBasedYear());
                    dateList.add(year + "-第" + String.format("%02d", weekOfYear)+"周");
                    currentDate = currentDate.plusWeeks(1);
                }
                break;
            case "monthly":
                // 遍历从 startDate 到 endDate 的每一月
                while (!currentDate.isAfter(endDate)) {
                    dateList.add(currentDate.format(DateTimeFormatter.ofPattern("yyyy-MM")));
                    currentDate = currentDate.plusMonths(1);
                }
                break;
            default:
                // 默认按日统计
                while (!currentDate.isAfter(endDate)) {
                    dateList.add(currentDate.format(DateTimeFormatter.ISO_LOCAL_DATE));
                    currentDate = currentDate.plusDays(1);
                }
                break;
        }
        response.setDateList(dateList);

        dateList.forEach(date -> {
            problemCountMap.put(date, 0);
            productCountMap.put(date, 0);
            problemDivideMapMap.put(date, new TreeMap<>());
        });

        // 根据周期统计激活数据
        Map<String, Integer> finalTotalProductCountMap = totalProductCountMap;
        dateList.forEach(date ->
                productCountMap.merge(date, finalTotalProductCountMap.getOrDefault(date, 0), Integer::sum)
        );

        // 根据周期统计问题数据
        List<FeedbackAnalysisDataFactorDto> feedbackAnalysisDataList = feedbackAnalysisDataMapper.selectFeedbackAnalysisData(
                params.getProducts(), params.getCategories(), null, null, params.getEndTime(), null);
        // 获取问题列表
        Set<String> tags = new LinkedHashSet<>();

        for (FeedbackAnalysisDataFactorDto feedback : feedbackAnalysisDataList) {
            String tag = problemMap.get(Integer.parseInt(feedback.getProblem()));
            tags.add(tag);
            String date;

            switch (PERIOD_MAP.get(params.getPeriod())) {
                case "weekly":
                    int weekOfYear = feedback.getFeedbackTime().get(WeekFields.ISO.weekOfWeekBasedYear());
                    int year = feedback.getFeedbackTime().get(WeekFields.ISO.weekBasedYear());
                    date =  year + "-第" + String.format("%02d", weekOfYear)+"周";  // "YYYY-WW" 格式
                    break;
                case "monthly":
                    date = feedback.getFeedbackTime()
                            .withDayOfMonth(1)
                            .format(DateTimeFormatter.ofPattern("yyyy-MM"));
                    break;
                default:
                    date = feedback.getFeedbackTime().format(DateTimeFormatter.ISO_LOCAL_DATE);
                    break;
            }
            problemCountMap.merge(date, feedback.getFactor()>0? feedback.getFactor():1, Integer::sum);
            if(problemDivideMapMap.get(date)==null){
                problemDivideMapMap.put(date, new TreeMap<>());
            }
            problemDivideMapMap.get(date).merge(tag, feedback.getFactor()>0? feedback.getFactor():1, Integer::sum);
        }

        Map<String,Integer> tagAccumulateMap = new TreeMap<>();
        Integer allAccumulate = 0;
        // region 设置统计问题的初始值
        Iterator<Map.Entry<String, Integer>> iterator = problemCountMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, Integer> entry = iterator.next();
            if (!dateList.contains(entry.getKey())) {
                allAccumulate += entry.getValue();
                iterator.remove();  // 使用 Iterator 的 remove() 方法删除元素
            }
        }
        Iterator<Map.Entry<String, Map<String,Integer>>> divideIterator = problemDivideMapMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, Map<String,Integer>> entry = divideIterator.next();
            if (!dateList.contains(entry.getKey())) {
                for (Map.Entry<String, Integer> detailEntry : entry.getValue().entrySet()) {
                    tagAccumulateMap.merge(entry.getKey(), detailEntry.getValue(), Integer::sum);
                }
                iterator.remove();
            }
        }
        // endregion

        //region 累计数据
        for (String date : dateList) {
            allAccumulate += problemCountMap.get(date);
            Map<String,Integer> problemDivideDateMap = problemDivideMapMap.getOrDefault(date, new TreeMap<>());

            for (Map.Entry<String,Integer> entity : problemDivideDateMap.entrySet()) {
                tagAccumulateMap.merge(entity.getKey(), entity.getValue(), Integer::sum);
            }

            problemCountMap.put(date, allAccumulate);
            problemDivideMapMap.put(date,tagAccumulateMap);
        }
        problemDivideMapMap.forEach((k,v)->{
            for (String tag : tags) {
                v.merge(tag, 0, Integer::sum);
            }
        });
        //endregion

        response.setProblemCountData(new ArrayList<>(problemCountMap.values()));
        response.setProductCountData(new ArrayList<>(productCountMap.values()));


        List<String> percents = new ArrayList<>();
        Map<String, List<String>> problemDetailPercentData = new TreeMap<>();
        Map<String, List<Integer>> problemDetailCountData = new TreeMap<>();
        for (int i = 0; i < response.getProductCountData().size(); i++) {
            if (response.getProductCountData().get(i) != 0) {
                double percent = (double) response.getProblemCountData().get(i) / response.getProductCountData().get(i);
                percents.add(String.format("%.2f", percent)); // 保留两位小数并转换为字符串
            } else {
                percents.add("0.00"); // 当分母为 0 时，添加 "0.00"
            }
            Map<String,Integer> problemDivideDateMap = problemDivideMapMap.getOrDefault(dateList.get(i), new TreeMap<>());
            for (Map.Entry<String,Integer> entity : problemDivideDateMap.entrySet()) {
                String percentStr = response.getProductCountData().get(i) != 0? String.format("%.2f",(double) entity.getValue() / response.getProductCountData().get(i)):"0.00";
                problemDetailPercentData.compute(entity.getKey(),(k,v)->{
                    if(v==null) v = new ArrayList<>();
                    v.add(percentStr);
                    return v;
                });
                problemDetailCountData.compute(entity.getKey(),(k,v)->{
                    if(v==null) v = new ArrayList<>();
                    v.add(entity.getValue());
                    return v;
                });
            }
        }
        response.setPercentData(percents);

        // 1. 按List<Integer> 的和倒序排序problemDetailCountData
        Map<String, List<Integer>> sortedCountMap = new LinkedHashMap<>();
        problemDetailCountData.entrySet()
                .stream()
                .sorted((e1, e2) -> Integer.compare(
                        e2.getValue().stream().mapToInt(Integer::intValue).sum(),
                        e1.getValue().stream().mapToInt(Integer::intValue).sum()
                ))
                .forEachOrdered(entry -> sortedCountMap.put(entry.getKey(), entry.getValue()));

        // 2. 按照sortedMap的顺序对problemDetailPercentData排序
        Map<String, List<String>> sortedPercentData = new LinkedHashMap<>();
        for (String key : sortedCountMap.keySet()) {
            if (problemDetailPercentData.containsKey(key)) {
                sortedPercentData.put(key, problemDetailPercentData.get(key));
            }
        }

        response.setProblemDetailPercentData(sortedPercentData);
        response.setProblemDetailCountData(sortedCountMap);
        return CommonResult.Success(response);
    }

    @Override
    public void exportTemplate(HttpServletResponse response) throws ServiceException {
        // 创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建表单
        XSSFSheet sheet = workbook.createSheet("用户反馈导入模板");

        // 设置表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("id");
        headerRow.createCell(1).setCellValue("产品品牌");
        headerRow.createCell(2).setCellValue("产品品类");
        headerRow.createCell(3).setCellValue("产品型号");
        headerRow.createCell(4).setCellValue("反馈内容");
        headerRow.createCell(5).setCellValue("咨询量");
        headerRow.createCell(6).setCellValue("点赞量");
        headerRow.createCell(7).setCellValue("反馈量");
        headerRow.createCell(8).setCellValue("反馈来源");
        headerRow.createCell(9).setCellValue("反馈时间");
        headerRow.createCell(10).setCellValue("反馈人");
        headerRow.createCell(11).setCellValue("反馈阶段");
        headerRow.createCell(12).setCellValue("反馈类型");
        headerRow.createCell(13).setCellValue("关联原文内容");
        headerRow.createCell(14).setCellValue("原文时间");
        headerRow.createCell(15).setCellValue("阅读标记");
        headerRow.createCell(16).setCellValue("三级标签");
        headerRow.createCell(17).setCellValue("用户态度");
        headerRow.createCell(18).setCellValue("问题现象");
        headerRow.createCell(19).setCellValue("备注");
        headerRow.createCell(20).setCellValue("ones ID");
        headerRow.createCell(21).setCellValue("备注：此内容仅供导入说明\n" +
                "产品品牌：必填，仅支持输入系统配置\n" +
                "产品品类：必填，仅支持输入系统配置\n" +
                "产品型号：必填，仅支持输入系统配置产品型号\n" +
                "反馈阶段：必填，仅支持输入系统配置反馈阶段\n" +
                "反馈来源：必填，仅支持输入系统配置反馈来源\n" +
                "反馈内容：必填，最多10000字符\n" +
                "咨询量：非必填，仅支持输入1-999999正整数\n" +
                "点赞量：非必填，仅支持输入1-999999正整数\n" +
                "浏览量：非必填，仅支持输入1-999999正整数\n" +
                "反馈人：非必填，最多20字符\n" +
                "反馈时间：非必填，仅支持输入时间格式yyyy-mm-dd\n" +
                "反馈类型：非必填，仅支持输入系统配置反馈类型\n" +
                "关联原文内容：非必填，最多10000字符\n" +
                "原文时间：非必填，仅支持输入时间格式yyyy-mm-dd\n" +
                "阅读标记：非必填，仅支持输入系统配置的阅读标记\n" +
                "注：如一条反馈有多条标签信息，则一条标签及相关信息独占一行，反馈内容合并单元格处理\n" +
                "三级标签：非必填，仅支持输入系统配置三级标签\n" +
                "用户态度：非必填，仅支持输入系统配置用户态度（好、中、差、未表态）\n" +
                "问题现象：非必填，仅支持输入系统配置问题现象\n" +
                "备注：非必填，最多输入50字符\n" +
                "ones ID：非必填，最多输入50字符");

        // 下拉框选项


        List<String> brands = titleName().get(0);
        List<String> categories = titleName().get(1);
        List<String> models = titleName().get(2);
        List<String> resources = titleName().get(4);
        List<String> stages = titleName().get(3);
        List<String> thirdLabels = titleName().get(5);
        List<String> type = Arrays.asList("主动反馈","引用反馈");
        List<String> remark = Arrays.asList("未标记","很重要","一般重要","不重要");
        List<String> attitude = Arrays.asList("好","中","差","未表态");


        // 为每列添加下拉框
        addDropDownListToColumn(workbook,sheet, brands, 1);
        addDropDownListToColumn(workbook,sheet, categories, 2);
        addDropDownListToColumn(workbook,sheet, models, 3);
        addDropDownListToColumn(workbook,sheet,resources,8);
        addDropDownListToColumn(workbook,sheet,stages,11);
        addDropDownListToColumn(workbook,sheet,type,12);
        addDropDownListToColumn(workbook,sheet,remark,15);
        addDropDownListToColumn(workbook,sheet,thirdLabels,16);
        addDropDownListToColumn(workbook,sheet,attitude,17);

        // 写入到文件
        try (FileOutputStream fileOut = new FileOutputStream("用户反馈导入模板.xlsx")) {

            String fileName = URLEncoder.encode("用户反馈导入模板.xlsx", StandardCharsets.UTF_8.name());
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ";filename*=utf-8''" + fileName);
            workbook.write(response.getOutputStream());

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        try {
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        System.out.println("Excel 文件生成成功！");
    }

//    private static void addDropDownListToColumn(XSSFSheet sheet, List<String> options, int columnIndex) {
//        // 转换选项列表为数组
//        String[] items = options.toArray(new String[0]);
//
//        // 定义下拉框数据
//        XSSFDataValidationHelper helper = new XSSFDataValidationHelper(sheet);
//        XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint) helper.createExplicitListConstraint(items);
//
//        // 设置应用下拉框的单元格范围
//        CellRangeAddressList addressList = new CellRangeAddressList(1, 200, columnIndex, columnIndex); // 从第2行开始到第101行
//
//        // 创建数据验证
//        XSSFDataValidation validation = (XSSFDataValidation) helper.createValidation(constraint, addressList);
//
//        // 设置验证属性
//        validation.setShowErrorBox(true);
//
//        // 添加到工作表
//        sheet.addValidationData(validation);
//    }

    private static void addDropDownListToColumn(XSSFWorkbook workbook, XSSFSheet sheet, List<String> options, int columnIndex) {
        // 创建隐藏工作表
        XSSFSheet hiddenSheet = workbook.createSheet("HiddenSheet" + columnIndex);
        workbook.setSheetVisibility(workbook.getSheetIndex(hiddenSheet), SheetVisibility.VERY_HIDDEN);

        // 将选项写入隐藏工作表
        for (int i = 0; i < options.size(); i++) {
            Row row = hiddenSheet.createRow(i);
            row.createCell(0).setCellValue(options.get(i));
        }

        // 定义名称范围
        Name namedRange = workbook.createName();
        String rangeName = "Options" + columnIndex;
        namedRange.setNameName(rangeName);
        namedRange.setRefersToFormula("HiddenSheet" + columnIndex + "!$A$1:$A$" + options.size());

        // 创建数据验证
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(rangeName);
        CellRangeAddressList addressList = new CellRangeAddressList(1, 200, columnIndex, columnIndex); // 设置作用范围
        DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);

        // 防止用户输入非法值
        dataValidation.setShowErrorBox(true);

        // 添加到工作表
        sheet.addValidationData(dataValidation);
    }



    private List<List<String>> titleName() {
        // 定义一个方法来获取指定类型的选项
        Function<Integer, List<String>> getOptionsByType = type -> {
            FeedbackConfigTypeParams params = new FeedbackConfigTypeParams();
            params.setType(type);
            return feedbackConfigMapper.selectByType(params)
                    .stream()
                    .map(FeedbackConfig::getName)
                    .collect(Collectors.toList());
        };

        // 定义需要查询的类型列表
        List<Integer> types =  Arrays.asList(1, 10, 2, 3, 4,7);

        // 遍历类型列表获取所有选项
        return types.stream()
                .map(getOptionsByType)
                .collect(Collectors.toList());
    }
}
